package com.ylfstu.jdbc.transaction_;

import com.ylfstu.jdbc.JdbcUtils.JdbcUtils;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @Author: Joy_yang
 * @Date: 2022/10/20 9:31
 * 演示jdbc中如何使用事务
 */
public class Transaction_ {

    @Test
    public void noTransaction() {
        //1. 得到连接
        Connection connection = null;
        //2. 执行SQL
        String sql = "update account set balance = balance - 100 where id = 1 ";
        String sql2 = "update account set balance = balance + 100 where id = 2 ";
        PreparedStatement preparedStatement = null;
        try {
            connection = JdbcUtils.getConnection(); //在默认情况下，connection对象默认自动提交，执行一句，提交一句
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();//执行第一条sql

            int i = 1 / 0;
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();//执行第二条sql

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JdbcUtils.close(null, preparedStatement, connection);
        }
    }

    //使用事务
    @Test
    public void useTransaction() {
        //1. 得到连接
        Connection connection = null;
        //2. 执行SQL
        String sql = "update account set balance = balance - 100 where id = 1 ";
        String sql2 = "update account set balance = balance + 100 where id = 2 ";
        PreparedStatement preparedStatement = null;
        try {
            connection = JdbcUtils.getConnection(); //在默认情况下，connection对象默认自动提交，执行一句，提交一句
            //将 connection 设置为不自动提交
            connection.setAutoCommit(false); //开启事务
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();//执行第一条sql

//            int i = 1 / 0; //抛出异常
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();//执行第二条sql

            //这里提交事务
            connection.commit();

        } catch (SQLException e) {
            //这里我们可以进行回滚，即撤销执行的sql
            //默认回滚到事务开始的状态
            System.out.println("执行发生了异常，撤销执行的sql");
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            //关闭资源
            JdbcUtils.close(null, preparedStatement, connection);
        }
    }
}






